# -*- coding: utf-8 -*-
import csv
from datetime import datetime
import cx_Oracle
import re

# 设置excel GBK编码
dsnStr = cx_Oracle.makedsn("192.168.110.213", 1521, "HNMES")
conn = cx_Oracle.connect("HNMES", "HNMES123", dsnStr, threaded=True)
cursor = conn.cursor()


def select_mes_po():
    sql = """with t as (
select STOCK_OUT_ID,max(D.MODEL) MODEL from FP_STOCK_OUT_DETAIL b   
join MES_CARTON c on B.CARTON_ID=C.CARTON_ID
join PRODUCTSPEC d on C.PRODUCT_CODE=D.PRODUCTSPECNAME
where b.CREATED_DATETIME>sysdate-200
 group by STOCK_OUT_ID
)
select distinct A.PO from FP_STOCK_OUT a join t on  A.STOCK_OUT_ID=t.STOCK_OUT_ID 
join (select distinct ATTR_CODE from BASE_DATADICT where ATTR_TYPE='LOGITECHPROJNO' and  PARENT_ID ='LOGITECHPROJNO') e on t.MODEL=e.ATTR_CODE
where a.CREATED_DATETIME>sysdate-200"""
    pos = cursor.execute(sql).fetchall()
    return pos


def CsvToDb(path, BATCH_ID, FILENAME):
    insert_line = 0
    f = open(path, encoding="gbk")
    csv_reader = csv.reader(f)
    CREATE_TIME = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
    CREATED_BY = "0A2831"
    for row_data in csv_reader:
        if row_data and insert_line > 0:
            value = list(row_data)
            value.append(CREATE_TIME)
            value.append(CREATED_BY)
            value.append(BATCH_ID)
            value.append(FILENAME)
            value = tuple(value)
            if re.search(r"MASTER", FILENAME):
                sql_insert = "insert into FP_EXPORT_MASTER values {}".format(value)
            elif re.search(r"TXN", FILENAME):
                sql_insert = "insert into FP_EXPORT_TXN values {}".format(value)
            else:
                return True
            try:
                cursor.execute(sql_insert)
            except:
                insert_line += 1
                continue
        insert_line += 1
    f.close()
    conn.commit()


def close_conn():
    cursor.close()
    conn.close()

